package DBLayer;
import java.sql.*;
import java.util.ArrayList;

import DBLayer.ConnectionDB;
import DBLayer.GetMax;
import ModelLayer.Guest;


public class GuestDB 
{
	private Connection conn;

public GuestDB() {
	conn = ConnectionDB.getInstance().getConn();
    }


public int insertGuest(Guest guest) {
	
	int rc = -1;
	
	String query = "INSERT INTO Guest(fname, lname, address, county, phone, email, travelAgency) VALUES('" +
			guest.getFname() + " ','" +
			guest.getLname() + " ','" +
			guest.getAddress() + " ','" +
			guest.getCountry() + " ','" +
			guest.getPhone() + " ','" +
			guest.getEmail() + " ','" +
			guest.getTravelAgency() + "')";
	
	try{
		Statement stmt = conn.createStatement();
		stmt.setQueryTimeout(5);
		rc = stmt.executeUpdate(query);
		stmt.close();
	}
	catch(SQLException e){
		System.out.println("Error : Exception catched in insertCity. " + e);
		e.printStackTrace();
	}
	
	return rc;
	
	}
 
	public Guest findGuestById(int id) {
		return singleWhere("id = " + id, false);
}
    public Guest findGuest(String userName, String password) {
        return singleWhere("id = studentId AND username = '" + userName + "' AND password = '" + password + "'", false);
    }

	public Guest findGuestByFname(String Fname) {
		return singleWhere("id = guestId AND Fname = " + Fname, false);
}
	public Guest findGuestByLname(String Lname) {
		return singleWhere("id = guestId AND Lname = " + Lname, false);
}
	public Guest findGuestByAddress(String Address) {
		return singleWhere("id = guestId AND Address = " + Address, false);
}
	public Guest findGuestByCountry(String Country) {
		return singleWhere("id = guestId AND Country = " + Country, false);
}
	public Guest findGuestByPhone(String Phone) {
		return singleWhere("id = guestId AND Phone = " + Phone, false);
}
	public Guest findGuestByEmail(String Email) {
		return singleWhere("id = guestId AND Email = " + Email, false);
}
	public Guest findGuestByTravelAgency(String TravelAgency) {
		return singleWhere("id = guestId AND TravelAgency = " + TravelAgency, false);
}
	public int updateGuest(Guest guest) {
		Guest guestObj = guest;
		int rc = -1;
		
		String queryGuest = "UPDATE Guest SET " +
                "id = '" + guestObj.getGuestId() + "', " +
				"fname = '" + guestObj.getFname() + "', " +
				"lname = '" + guestObj.getLname() + "', " +
				"address = '" + guestObj.getAddress() + "', " +
				"country = '" + guestObj.getCountry() + "', " +
				"phone = '" + guestObj.getPhone() + "', " +
				"email = '" + guestObj.getEmail() + "', " +
				"travelAgency = '" + guestObj.getTravelAgency();
		
		System.out.println(queryGuest);
		
		try {
			Statement stmtGuest = conn.createStatement();
			stmtGuest.setQueryTimeout(5);
			rc = stmtGuest.executeUpdate(queryGuest);
			stmtGuest.close();
		}
		catch (Exception e) {
			System.out.println("Update exception in Guest db:" +e);
		}
		return rc;
		
		}
	
	public int deleteGuest(int id) {
		int rc = -1;
		
		String deleteGuest = "DELETE FROM Guest WHERE guestId = '" + id + "'";
		
		try {
			ConnectionDB.startTransation();
			Statement stmt = conn.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(deleteGuest);
			stmt.close();
			ConnectionDB.commitTransation();
		}
		catch (Exception e) {
			System.out.println(e);
			ConnectionDB.rollbackTransation();
		}
		return rc;
		
		}
	
	private Guest singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		Guest guestObj = new Guest();
		
		String query = buildQuery(wClause);
		System.out.println(query);
		
		try {
			Statement stmt = conn.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if (results.next()) {
				guestObj = buildGuest(results);
				stmt.close();
			}
			else {
				guestObj = null;
			}
		}
		catch (Exception e) {
			System.out.println("Query excption: " +e);
		}
		return guestObj;
	}

    private ArrayList<Guest> miscWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        ArrayList<Guest> list = new ArrayList<Guest>();

        String query = buildQuery(wClause);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next()) {
                Guest guestObj = buildGuest(results);
                list.add(guestObj);
            }
            System.out.println(query);

            stmt.close();

        }
        catch (Exception e) {
            System.out.println("Query exception: " +e);
            e.printStackTrace();
        }

        return list;
    }

    private String buildQuery(String wClause) {
        String query = "SELECT id, fname, lname, address, country, phone, email, travelAgency FROM Guest";

        if (wClause.length()>0) {
            query = query + "WHERE " + wClause;
        }

        return query;
    }

    private Guest buildGuest(ResultSet results) {

        Guest guestObj = new Guest();

        try {
            guestObj.setId(results.getInt("id"));
            guestObj.setFname(results.getString("fname"));
            guestObj.setLname(results.getString("lname"));
            guestObj.setAddress(results.getString("address"));
            guestObj.setCountry(results.getString("country"));
            guestObj.setPhone(results.getString("phone"));
            guestObj.setEmail(results.getString("email"));
            guestObj.setTravelAgency(results.getString("travelAgency"));
        }
        catch (Exception e) {
            System.out.println("Error building Guest object" +e);
        }

        return guestObj;
    }

    public int guestGetMax() {
        int nextId = GetMax.getMaxId("SELECT max(id) from Guest");

        nextId = nextId +1;

        return nextId;
    }


	}